--region [dbo].[AddFriend]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[AddFriend]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[AddFriend]') IS NOT NULL
	DROP PROCEDURE [dbo].[AddFriend]
GO

CREATE PROCEDURE [dbo].[AddFriend]
	@HostUserId int,
	@HostUserName nvarchar(50),
	@FriendUserId int,
	@FriendUserName nvarchar(50),
	@State int,
	@PostTime datetime,
	@UpdateTime datetime,
	@AppMsg nvarchar(max)
AS

INSERT INTO [Friend] (
	[HostUserId],
	[HostUserName],
	[FriendUserId],
	[FriendUserName],
	[State],
	[PostTime],
	[UpdateTime],
	[AppMsg]
) VALUES (
	@HostUserId,
	@HostUserName,
	@FriendUserId,
	@FriendUserName,
	@State,
	@PostTime,
	@UpdateTime,
	@AppMsg
)

GO
--endregion


--region [dbo].[UpdateFriend]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateFriend]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateFriend]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateFriend]
GO

CREATE PROCEDURE [dbo].[UpdateFriend]
	@Id int,
	@HostUserId int,
	@HostUserName nvarchar(50),
	@FriendUserId int,
	@FriendUserName nvarchar(50),
	@State int,
	@PostTime datetime,
	@UpdateTime datetime
AS

UPDATE [Friend] SET
	[HostUserId] = @HostUserId,
	[HostUserName] = @HostUserName,
	[FriendUserId] = @FriendUserId,
	[FriendUserName] = @FriendUserName,
	[State] = @State,
	[PostTime] = @PostTime,
	[UpdateTime] = @UpdateTime
WHERE
	[Id] = @Id

GO
--endregion


--region [dbo].[UpdateFriendState]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateFriendState]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateFriendState]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateFriendState]
GO

CREATE PROCEDURE [dbo].[UpdateFriendState]
	@WhereCondition nvarchar(512),
	@State int
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
UPDATE [Friend] SET
	[State] = ' + STR(@State) + '
WHERE
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[DelFriend]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[DelFriend]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[DelFriend]') IS NOT NULL
	DROP PROCEDURE [dbo].[DelFriend]
GO

CREATE PROCEDURE [dbo].[DelFriend]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
DELETE FROM
	[Friend]
WHERE
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetFriend]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetFriend]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetFriend]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetFriend]
GO

CREATE PROCEDURE [dbo].[GetFriend]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT
	[Id],
	[HostUserId],
	[HostUserName],
	[FriendUserId],
	[FriendUserName],
	[State],
	[PostTime],
	[UpdateTime]
FROM
	[Friend]
WHERE
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetFriendList]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetFriendList]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetFriendList]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetFriendList]
GO

CREATE PROCEDURE [dbo].[GetFriendList]
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT([Friend].[Id]) 
FROM
	[Friend], [UserInfo]
WHERE
	'
IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT TOP ' + STR(@pagesize) + '
	[UserInfo].[Id],
	[UserInfo].[UserName],
	[UserInfo].[Email],
	''Age'' = DATEDIFF(year, [UserInfo].[Birthday], getdate()),
	[UserInfo].[Gender],
	''Province'' = dbo.GetDistrictName([ProvinceId]),
	''City'' = dbo.GetDistrictName([CityId]),
	[UserInfo].[Description],
	[Avatar],	
	''FriendState'' = [State],
	''ApplyFriendTime'' = [PostTime],
	''ApplyMsg'' = [AppMsg]
FROM
	[Friend], [UserInfo]
WHERE
	'

IF @PageIndex > 1
BEGIN
SET @SQL = @SQL + '
	AND [Friend].[Id] < 
	( 
	SELECT MIN([Friend].[Id])  
	FROM ( 
	SELECT TOP 
		' + STR((@PageIndex-1)*@PageSize) + ' [Friend].[Id] 
	FROM [Friend], [UserInfo]
	WHERE
	' 

	IF @WhereCondition <> ''
	BEGIN
		SET @SQL = @SQL + @WhereCondition
	END

	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + '
	ORDER BY
		' + @OrderByExpression + ') AS tblTmp ) '
	END
	ELSE
	BEGIN
		SET @SQL = @SQL + ') AS tblTmp ) '
	END

	IF @WhereCondition <> ''
	BEGIN
	SET @SQL = @SQL + @WhereCondition
	END
	
	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + '
	ORDER BY
		' + @OrderByExpression 
	END	
END
ELSE 
BEGIN
	IF @WhereCondition <> ''
	BEGIN
	SET @SQL = @SQL + @WhereCondition
	END
	
	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + ' 
	ORDER BY
		' + @OrderByExpression 
	END	
END

EXEC sp_executesql @SQL

GO
--endregion


